using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Transactions;
using System.Xml;
using Common.Utilities;

namespace Workflows.Components.DALC
{
    /// <summary>
    /// 
    /// </summary>
    public class WorkflowDesignDALC
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<WorkflowDesign> GetAllWorkflowDesigns()
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            List<WorkflowDesign> wfDesigns=new List<WorkflowDesign>();
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetAllWorkflowDesigns";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                WorkflowEngine engine=new WorkflowEngine();

                while(reader.Read())
                { 
                    WorkflowDesign wfDesign=new WorkflowDesign();
                    wfDesign.WorkflowGuid=new Guid(reader["WorkflowGuid"].ToString());
                    wfDesign.WorkflowName = reader["WorkflowName"].ToString();
                    wfDesign.Version=new Version(reader["Version"].ToString());
                    wfDesign.CreationTime = DateTime.Parse(reader["CreationTime"].ToString());
                    wfDesign.ModificationTime = DateTime.Parse(reader["ModificationTime"].ToString());
                    wfDesign.Author = reader["Author"].ToString();
                    wfDesign.Description = reader["Description"].ToString();
                    wfDesign.FailureNotifyEmailAddress = reader["FailureNotifyEmailAddress"].ToString();
                    wfDesign.DesignDocumentFilePath = reader["DesignDocumentFilePath"].ToString();
                    XmlDocument xdoc=new XmlDocument();
                    xdoc.LoadXml(reader["XDoc"].ToString());
                    wfDesign.Components = engine.GetWorkflowComponents(xdoc).ToArray();
                    wfDesign.Connectors = engine.GetConnectors(xdoc).ToArray();
                    wfDesign.GlobalVariables = engine.GetGlovalVariables(xdoc).ToArray();
                    wfDesign.CheckOutBy = reader["CheckOutBy"].ToString();
                    wfDesign.CheckOutTime = DateTime.Parse(reader["CheckOutTime"].ToString());
                    wfDesign.IsLocked = bool.Parse(reader["IsLocked"].ToString());

                    wfDesigns.Add(wfDesign);
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfDesigns;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wfDesign"></param>
        public static void DisableWorkflowDesign(WorkflowDesign wfDesign)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            TransactionScope scope = new TransactionScope();
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SaveWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter wfidParam = new SqlParameter("@WorkflowGuid", wfDesign.WorkflowGuid.ToString());
                SqlParameter wfNameParam = new SqlParameter("@WorkflowName", wfDesign.WorkflowName);
                SqlParameter verParam = new SqlParameter("@Version", wfDesign.Version.ToString());
                SqlParameter createTimeParam = new SqlParameter("@CreationTime", wfDesign.CreationTime);
                SqlParameter modTimeParam = new SqlParameter("@ModificationTime", wfDesign.ModificationTime);
                SqlParameter authorParam = new SqlParameter("@Author", wfDesign.Author);
                SqlParameter descParam = new SqlParameter("@Description", wfDesign.Description);
                SqlParameter emailParam =
                    new SqlParameter("@FailureNotifyEmailAddress", wfDesign.FailureNotifyEmailAddress);
                XmlDocument xdoc = wfDesign.SaveAsXml();
                SqlParameter xdocParam = new SqlParameter("@XDoc", xdoc.OuterXml);
                SqlParameter pathParam = new SqlParameter("@DesignDocumentFilePath", wfDesign.DesignDocumentFilePath);
                SqlParameter checkOutTimeParam = new SqlParameter("@CheckOutTime", wfDesign.CheckOutTime);
                SqlParameter checkoutByParam = new SqlParameter("@CheckOutBy", wfDesign.CheckOutBy);
                SqlParameter lockedParam = new SqlParameter("@IsLocked", wfDesign.IsLocked);
                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                wfidParam, wfNameParam, verParam, createTimeParam,
                                                modTimeParam, authorParam, descParam, emailParam, 
                                                xdocParam, pathParam, checkoutByParam,
                                                checkOutTimeParam, lockedParam
                                            });
                cmd.ExecuteNonQuery();
                scope.Complete();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wfDesign"></param>
        public static void CheckoutWorkflowDesign(WorkflowDesign wfDesign)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            TransactionScope scope = new TransactionScope();
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SaveWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter wfidParam = new SqlParameter("@WorkflowGuid", wfDesign.WorkflowGuid.ToString());
                SqlParameter wfNameParam = new SqlParameter("@WorkflowName", wfDesign.WorkflowName);
                SqlParameter verParam = new SqlParameter("@Version", wfDesign.Version.ToString());
                SqlParameter createTimeParam = new SqlParameter("@CreationTime", wfDesign.CreationTime);
                SqlParameter modTimeParam = new SqlParameter("@ModificationTime", wfDesign.ModificationTime);
                SqlParameter authorParam = new SqlParameter("@Author", wfDesign.Author);
                SqlParameter descParam = new SqlParameter("@Description", wfDesign.Description);
                SqlParameter emailParam =
                    new SqlParameter("@FailureNotifyEmailAddress", wfDesign.FailureNotifyEmailAddress);
                XmlDocument xdoc = wfDesign.SaveAsXml();
                SqlParameter xdocParam = new SqlParameter("@XDoc", xdoc.OuterXml);
                SqlParameter pathParam = new SqlParameter("@DesignDocumentFilePath", wfDesign.DesignDocumentFilePath);
                SqlParameter checkOutTimeParam = new SqlParameter("@CheckOutTime", wfDesign.CheckOutTime);
                SqlParameter checkoutByParam = new SqlParameter("@CheckOutBy", wfDesign.CheckOutBy);
                SqlParameter lockedParam = new SqlParameter("@IsLocked", wfDesign.IsLocked);
                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                wfidParam, wfNameParam, verParam, createTimeParam,
                                                modTimeParam, authorParam, descParam, emailParam, 
                                                xdocParam, pathParam, checkoutByParam,
                                                checkOutTimeParam, lockedParam
                                            });
                cmd.ExecuteNonQuery();
                scope.Complete();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wfDesign"></param>
        public static void SaveWorkflowDesign(WorkflowDesign wfDesign)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            TransactionScope scope=new TransactionScope();
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SaveWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                
                SqlParameter wfidParam=new SqlParameter("@WorkflowGuid",wfDesign.WorkflowGuid.ToString());
                SqlParameter wfNameParam=new SqlParameter("@WorkflowName",wfDesign.WorkflowName);
                SqlParameter verParam = new SqlParameter("@Version", wfDesign.Version.ToString());
                SqlParameter createTimeParam = new SqlParameter("@CreationTime", wfDesign.CreationTime);
                SqlParameter modTimeParam = new SqlParameter("@ModificationTime", wfDesign.ModificationTime);
                SqlParameter authorParam = new SqlParameter("@Author", wfDesign.Author);
                SqlParameter descParam = new SqlParameter("@Description", wfDesign.Description);
                SqlParameter emailParam =
                    new SqlParameter("@FailureNotifyEmailAddress", wfDesign.FailureNotifyEmailAddress);
                //SqlParameter imgParam = new SqlParameter("@WorkflowDiagramImage", wfDesign.WorkflowDiagramImage);
                //SqlParameter diagramParam = new SqlParameter("@WorkflowDiagram", wfDesign.WorkflowDiagram);

                XmlDocument xdoc = wfDesign.SaveAsXml();
                SqlParameter xdocParam = new SqlParameter("@XDoc", xdoc.OuterXml);
                SqlParameter pathParam = new SqlParameter("@DesignDocumentFilePath", wfDesign.DesignDocumentFilePath);
                SqlParameter checkOutTimeParam = new SqlParameter("@CheckOutTime", wfDesign.CheckOutTime);
                SqlParameter checkoutByParam = new SqlParameter("@CheckOutBy", wfDesign.CheckOutBy);
                SqlParameter lockedParam = new SqlParameter("@IsLocked", wfDesign.IsLocked);
                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                wfidParam, wfNameParam, verParam, createTimeParam,
                                                modTimeParam, authorParam, descParam, emailParam, 
                                                xdocParam, pathParam, checkoutByParam,
                                                checkOutTimeParam, lockedParam
                                            });
                cmd.ExecuteNonQuery();

                cmd.CommandText = "delete WorkflowComponents where WorkflowID='" + wfDesign.WorkflowGuid.ToString() +
                                  "'";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                if(wfDesign.Components !=null && wfDesign.Components.Length>0)
                {
                    foreach (WorkflowComponentBase wfComponent in wfDesign.Components)
                    {
                        cmd.CommandText =
                            "insert into WorkflowComponents " +
                            "(ComponentID, WorkflowID, ComponentName) values ('" +
                            wfComponent.ComponentID.ToString() + "','" +
                            wfDesign.WorkflowGuid.ToString() + "','" +
                            wfComponent.ComponentName + "')";
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }    
                }
                cmd.CommandText = "delete WorkflowTriggerInstance where WorkflowDesignID='" +
                                  wfDesign.WorkflowGuid.ToString() + "'";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                scope.Complete();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }

            if (File.Exists(wfDesign.DesignDocumentFilePath))
            {
                string folderPath = Path.GetDirectoryName(wfDesign.DesignDocumentFilePath);
                string fileName = Path.GetFileNameWithoutExtension(wfDesign.DesignDocumentFilePath);
                string woxFilePath = Path.Combine(folderPath, fileName + ".wox");
                if(File.Exists(woxFilePath))
                {
                    BackgroundWorker woxWorker=new BackgroundWorker();
                    woxWorker.DoWork += new DoWorkEventHandler(woxWorker_DoWork);
                    woxWorker.RunWorkerAsync(wfDesign);
                }
                string pngFilePath = Path.Combine(folderPath, fileName + ".png");
                if(File.Exists(pngFilePath))
                {
                    BackgroundWorker pngWorker=new BackgroundWorker();
                    pngWorker.DoWork += new DoWorkEventHandler(pngWorker_DoWork);
                    pngWorker.RunWorkerAsync(wfDesign);
                }
            }
        }

        static void pngWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            WorkflowDesign wfDesign = (WorkflowDesign)e.Argument;
            string folderPath = Path.GetDirectoryName(wfDesign.DesignDocumentFilePath);
            string fileName = Path.GetFileNameWithoutExtension(wfDesign.DesignDocumentFilePath);
            string pngFilePath = Path.Combine(folderPath, fileName + ".png");
            if (File.Exists(pngFilePath))
            {
                string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter paramWfID = new SqlParameter("@WorkflowGuid", wfDesign.WorkflowGuid.ToString());
                    SqlParameter paramVersion = new SqlParameter("@Version", wfDesign.Version.ToString());
                    SqlParameter paramAuthor = new SqlParameter("@Author", wfDesign.Author);
                    SqlParameter paramUpdatedOn = new SqlParameter("@UpdatedOn", wfDesign.ModificationTime);
                    SqlParameter paramPngFile = new SqlParameter("@PngFile", DBNull.Value);
                    FileInfo fileInfo = new FileInfo(pngFilePath);
                    byte[] buffer = new byte[fileInfo.Length];
                    BinaryReader reader = new BinaryReader(File.OpenRead(pngFilePath));
                    reader.Read(buffer, 0, buffer.Length);
                    reader.Close();
                    paramPngFile.Value = buffer;
                    cmd.CommandText = "SavePngFile";
                    cmd.Parameters.AddRange(new SqlParameter[]
                                                {
                                                    paramWfID, paramVersion, paramAuthor, paramUpdatedOn, paramPngFile
                                                });
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        static void woxWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            WorkflowDesign wfDesign = (WorkflowDesign) e.Argument;
            string folderPath = Path.GetDirectoryName(wfDesign.DesignDocumentFilePath);
            string fileName = Path.GetFileNameWithoutExtension(wfDesign.DesignDocumentFilePath);
            string woxFilePath = Path.Combine(folderPath, fileName + ".wox");
            if (File.Exists(woxFilePath))
            {
                string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter paramWfID=new SqlParameter("@WorkflowGuid",wfDesign.WorkflowGuid.ToString());
                    SqlParameter paramVersion=new SqlParameter("@Version",wfDesign.Version.ToString());
                    SqlParameter paramAuthor=new SqlParameter("@Author",wfDesign.Author);
                    SqlParameter paramUpdatedOn=new SqlParameter("@UpdatedOn",wfDesign.ModificationTime);
                    SqlParameter paramWoxFile=new SqlParameter("@WoxFile",DBNull.Value);
                    FileInfo fileInfo=new FileInfo(woxFilePath);
                    byte[] buffer=new byte[fileInfo.Length];
                    BinaryReader reader=new BinaryReader(File.OpenRead(woxFilePath));
                    reader.Read(buffer, 0, buffer.Length);
                    reader.Close();
                    paramWoxFile.Value = buffer;
                    cmd.CommandText = "SaveWoxFile";
                    cmd.Parameters.AddRange(new SqlParameter[]
                                                {
                                                    paramWfID, paramVersion, paramAuthor, paramUpdatedOn, paramWoxFile
                                                });
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wfID"></param>
        /// <returns></returns>
        public static byte[] GetWoxFile(Guid wfID)
        {
            byte[] buffer = null;
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetWoxFile";
                SqlParameter wfIDParam=new SqlParameter("@WorkflowGuid",wfID.ToString());
                cmd.Parameters.Add(wfIDParam);
                SqlDataReader reader = cmd.ExecuteReader();
                if(reader!=null && reader.Read())
                {
                    buffer = reader[0] as byte[];
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return buffer;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wfID"></param>
        /// <returns></returns>
        public static byte[] GetPngFile(Guid wfID)
        {
            byte[] buffer = null;
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetPngFile";
                SqlParameter wfIDParam = new SqlParameter("@WorkflowGuid", wfID.ToString());
                cmd.Parameters.Add(wfIDParam);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    buffer = reader[0] as byte[];
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return buffer;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="componentID"></param>
        /// <returns></returns>
        public static Guid GetWorkflowIDByComponentID(Guid componentID)
        {
            Guid wfID = Guid.Empty;
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select WorkflowID from WorkflowComponents where ComponentID='" +
                                  componentID.ToString() + "'";
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    wfID=new Guid(reader.GetString(0));
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfID;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="componentID"></param>
        /// <returns></returns>
        public static WorkflowComponentBase GetWorkflowComponent(Guid componentID)
        {
            Guid wfID = WorkflowDesignDALC.GetWorkflowIDByComponentID(componentID);
            WorkflowDesign wfDesign = WorkflowDesignDALC.GetWorkflowDesign(wfID);
            if (wfDesign != null)
            {
                WorkflowComponentBase wfComponent = wfDesign.GetComponent(componentID);
                return wfComponent;
            }
            else
                return null;
        }

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="workstationName"></param>
        ///// <param name="filePath"></param>
        ///// <returns></returns>
        //public static WorkflowDesign GetWorkflowDesignByFilePath(string filePath)
        //{
        //    WorkflowEngine engine=new WorkflowEngine();
        //    WorkflowDesign wfDesign=new WorkflowDesign();
        //    string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
        //    SqlConnection conn = new SqlConnection(connStr);
        //    try
        //    {
        //        conn.Open();
        //        SqlCommand cmd = conn.CreateCommand();
        //        cmd.CommandText = "GetWorkflowDesignByFilePath";
        //        cmd.CommandType = CommandType.StoredProcedure;
        //        SqlParameter filePathParam = new SqlParameter("@FilePath", filePath);
        //        cmd.Parameters.AddRange(new SqlParameter[] { machineNameParam, filePathParam});
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            wfDesign.WorkflowGuid = new Guid(reader["WorkflowGuid"].ToString());
        //            wfDesign.WorkflowName = reader["WorkflowName"].ToString();
        //            wfDesign.Version = new Version(reader["Version"].ToString());
        //            wfDesign.CreationTime = DateTime.Parse(reader["CreationTime"].ToString());
        //            wfDesign.ModificationTime = DateTime.Parse(reader["ModificationTime"].ToString());
        //            wfDesign.Author = reader["Author"].ToString();
        //            wfDesign.Description = reader["Description"].ToString();
        //            wfDesign.FailureNotifyEmailAddress = reader["FailureNotifyEmailAddress"].ToString();
        //            wfDesign.DesignDocumentFilePath = reader["DesignDocumentFilePath"].ToString();
        //            XmlDocument xdoc = new XmlDocument();
        //            xdoc.LoadXml(reader["XDoc"].ToString());
        //            wfDesign.Components = engine.GetWorkflowComponents(xdoc).ToArray();
        //            wfDesign.Connectors = engine.GetConnectors(xdoc).ToArray();
        //            wfDesign.GlobalVariables = engine.GetGlovalVariables(xdoc).ToArray();
        //            wfDesign.CheckOutBy = reader["CheckOutBy"].ToString();
        //            wfDesign.CheckOutTime = DateTime.Parse(reader["CheckOutTime"].ToString());
        //            wfDesign.IsLocked = bool.Parse(reader["IsLocked"].ToString());
        //        }
        //        reader.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        ExceptionHandler.HandlesException(ex);
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //    return wfDesign;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workflowID"></param>
        /// <returns></returns>
        public static WorkflowDesign GetWorkflowDesign(Guid workflowID)
        {
            WorkflowEngine engine = new WorkflowEngine();
            WorkflowDesign wfDesign = new WorkflowDesign();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter wfIDParam=new SqlParameter("@WorkflowGuid",workflowID.ToString());
                cmd.Parameters.Add(wfIDParam);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    wfDesign.WorkflowGuid = new Guid(reader["WorkflowGuid"].ToString());
                    wfDesign.WorkflowName = reader["WorkflowName"].ToString();
                    wfDesign.Version = new Version(reader["Version"].ToString());
                    wfDesign.CreationTime = DateTime.Parse(reader["CreationTime"].ToString());
                    wfDesign.ModificationTime = DateTime.Parse(reader["ModificationTime"].ToString());
                    wfDesign.Author = reader["Author"].ToString();
                    wfDesign.Description = reader["Description"].ToString();
                    wfDesign.FailureNotifyEmailAddress = reader["FailureNotifyEmailAddress"].ToString();
                    wfDesign.DesignDocumentFilePath = reader["DesignDocumentFilePath"].ToString();
                    XmlDocument xdoc = new XmlDocument();
                    xdoc.LoadXml(reader["XDoc"].ToString());
                    wfDesign.Components = engine.GetWorkflowComponents(xdoc).ToArray();
                    wfDesign.Connectors = engine.GetConnectors(xdoc).ToArray();
                    wfDesign.GlobalVariables = engine.GetGlovalVariables(xdoc).ToArray();
                    wfDesign.CheckOutBy = reader["CheckOutBy"].ToString();
                    wfDesign.CheckOutTime = DateTime.Parse(reader["CheckOutTime"].ToString());
                    wfDesign.IsLocked = bool.Parse(reader["IsLocked"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfDesign;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="runID"></param>
        /// <returns></returns>
        public static WorkflowDesign GetWorkflowDesignByRun(Guid runID)
        {
            Run run = RunDALC.GetRun(runID);
            return WorkflowDesignDALC.GetWorkflowDesign(run.WorkflowID);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workflowID"></param>
        public static void DeleteWorkflowDesign(Guid workflowID)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DeleteWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter wfIDParam = new SqlParameter("@WorkflowGuid", workflowID.ToString());
                cmd.Parameters.Add(wfIDParam);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
